
CREATE OR REPLACE FUNCTION fun_retrieve_call_report(
    character varying,
    character varying,
    character varying,
    refcursor)
  RETURNS refcursor AS
$BODY$DECLARE
                
        	in_string	ALIAS FOR $1;
        	fdate	ALIAS FOR $2;
        	tdate	ALIAS FOR $3;
        	datacursor	ALIAS FOR $4;
     		
v_fdate date;
v_tdate date;

          BEGIN

IF in_string!='CLOSED' THEN
open datacursor for

	 select callentry,
	       call_date,
		complaint_id,
		customer_name,
		area,
		name,call_details,item,
		emp_name,
		(case when ca.call_status='PFPE' then 'PENDING' else call_status end) as call_status,
	
		(case when ca.call_status!='CLOSED' then ((current_date-creation_date)||' DAYS') ELSE '-' END) as pending
       
 from 
	(
          select array_to_string(array_accum(ce.callentry_id),'$') as callentry,
	       to_char(ca.creation_date,'dd-mm-yyyy') as call_date,
		ce.complaint_id,cm.customer_name,cm.area,cn.name,coalesce(ce.call_details,'-') as call_details,
		array_to_string(array_accum(im.item_name),' AND<br>')as  item,(em.name||' '||em.last_name) as emp_name,
		(case when ca.callstatus='PFP' then 'PFPE' else call_status end) as call_status
		,ca.creation_date
	from callentry ce,customer_master cm,call_nature cn,call_assigned ca,item_master im,employee_master em--,call_performed cp
	where ce.customer_id=cm.customer_id 
	--and cp.cust_id=cm.customer_id 
	and ce.callentry_id=ca.callentry_id
	--and cp.complaint_id=ca.complaint_id
	and ca.item_id=im.item_id
	and ca.employee_id=em.emp_id	
 	and  ce.call_nature_id=cn.call_nature_id 
        and ca.creation_date >=(to_date(fdate,'dd-mm-yyyy')) and ca.creation_date <=to_date(tdate,'dd-mm-yyyy')
        and ce.status='V'    
	group by em.name,em.last_name,ca.callstatus,ca.creation_date,ce.call_status,ce.complaint_id,cm.customer_name,cm.area,cn.name,coalesce(ce.call_details,'-'),ce.call_lockdate,ce.customer_id, ca.employee_id
	order by ca.creation_date,call_lockdate
	) as ca
	where
	ca.call_status like (case when in_string!='ALL' then in_string else '%E%' end)
	
	;

	
	


ELSE 
open datacursor for


   select array_to_string(array_accum(ce.callentry_id),'$'),
	  to_char(cp.issue_closed_date,'dd-mm-yyyy'),ce.complaint_id,cm.customer_name,cm.area,cn.name,coalesce(ce.call_details,'-'), array_to_string(array_accum(im.item_name),' and<br>'),(em.name||' '||em.last_name) as emp_name,
		ca.callstatus,action_taken
	
	from callentry ce,customer_master cm,call_nature cn,call_assigned ca,item_master im,employee_master em,call_performed cp
	where ce.customer_id=cm.customer_id 
	and cp.cust_id=cm.customer_id 
	and ce.callentry_id=ca.callentry_id
	and cp.complaint_id=ca.complaint_id
	and ca.item_id=im.item_id
	and ca.employee_id=em.emp_id	
 	and  ce.call_nature_id=cn.call_nature_id 
	and callstatus='CLOSED'
	and ce.status='V' --and
	and cp.issue_closed_date >=(to_date(fdate,'dd-mm-yyyy')) and cp.issue_closed_date <=to_date(tdate,'dd-mm-yyyy')----(to_date(fdate,'dd-mm-yyy'))
	group by em.name,em.last_name,cp.action_taken,ca.callstatus,ca.creation_date,cp.issue_closed_date,ce.complaint_id,cm.customer_name,cm.area,cn.name,coalesce(ce.call_details,'-'),ce.call_lockdate,ce.customer_id, ca.employee_id
	order by cp.issue_closed_date;
	--order by cp.issue_closed_date,call_lockdate,ce.customer_id;


END IF;




          return datacursor;
        	
        END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
